using System;
using System.Data.SqlClient;
using System.Data;
using Microsoft.ApplicationBlocks.Data;
using System.Collections;
using System.Xml;
using Umbraco.Ecommerce.BusinessLogic.Collections;

namespace Umbraco.Ecommerce.BusinessLogic
{
	/// <summary>
	/// Summary description for Order.
	/// </summary>
	public class Order
	{
		private OrderLineCollection _OrderLines = new OrderLineCollection();
		private int _OrderId;
		private bool isInitialized = false;
		private string _dbdsn = umbraco.GlobalSettings.DbDSN;

		public Order(int id) 
		{
			_OrderId = id;
		}

		public Order(int id, string dbdsn) 
		{
			_OrderId = id;
			_dbdsn = dbdsn;
		}

		public string SessionId {
			get {
				return SqlHelper.ExecuteScalar(_dbdsn,CommandType.Text,"select sessionId from ecommerceOrder where id = " + _OrderId).ToString();
			}
		}

		public void Delete() 
		{
			SqlHelper.ExecuteNonQuery(_dbdsn,CommandType.Text,"delete from ecommerceOrder where id = " +_OrderId);
			SqlHelper.ExecuteNonQuery(_dbdsn,CommandType.Text,"delete from ecommerceOrderLines where OrderId = " +_OrderId);
		}

		public BusinessLogic.PaymentMethod PaymentMethod 
		{
			get 
			{
				return new PaymentMethod(new Guid(SqlHelper.ExecuteScalar(_dbdsn,CommandType.Text,"select paymentMethod from ecommerceOrder where id = " + _OrderId).ToString()));
			}
		}

		public int OrderId 
		{
			get {return _OrderId;}
		}

		public static void DeleteUnconfirmedOrdersFromSession(string SessionId) {
			SqlParameter[] SqlParams = new SqlParameter[] { new SqlParameter("@sessionId",SessionId),
															  new SqlParameter("@status",Status.NotConfirmed.ToString())};
			
			SqlDataReader dr = SqlHelper.ExecuteReader(umbraco.GlobalSettings.DbDSN,CommandType.Text,"select id from ecommerceOrder where sessionId = @sessionId and status = @status",SqlParams);
			while (dr.Read())
			{
				new Order(int.Parse(dr["id"].ToString())).Delete();
			}
			dr.Close();
		}

		public static ArrayList GetSessionOrders(string SessionId) 
		{
			SqlParameter[] SqlParams = new SqlParameter[] {new SqlParameter("@sessionId",SessionId)};
			SqlDataReader dr  = SqlHelper.ExecuteReader(umbraco.GlobalSettings.DbDSN,CommandType.Text,"Select id from ecommerceOrder where sessionId = @sessionId",SqlParams);
			ArrayList tmp = new ArrayList();
			while (dr.Read()){
				tmp.Add(new Order(int.Parse(dr["id"].ToString())));
			}
			dr.Close();
			return tmp;
		}

		public static bool SessionHasUnConfirmedOrder(string sessionId) 
		{
			SqlParameter[] SqlParams = new SqlParameter[] { new SqlParameter("@sessionId",sessionId),
															  new SqlParameter("@status",Status.NotConfirmed.ToString())};
			return (int.Parse(SqlHelper.ExecuteScalar(umbraco.GlobalSettings.DbDSN,CommandType.Text,"Select count(id) as tmp from ecommerceOrder where sessionId = @sessionId and status = @status",SqlParams).ToString()) >0);
		}
		
		private decimal Total 
		{
			get 
			{
				return decimal.Parse(SqlHelper.ExecuteScalar(_dbdsn,CommandType.Text,"select total from ecommerceOrder where id = " + _OrderId).ToString(), BusinessLogic.Store.InputNumberFormatInfo);
			}
			set {
				SqlParameter[] SqlParams = new SqlParameter[] {
						new SqlParameter("@total", value)
				};
				
				SqlHelper.ExecuteNonQuery(_dbdsn,CommandType.Text,
					"update ecommerceOrder set total =  @total where id= " + _OrderId,SqlParams);
			}
		}

		public decimal TotalExVAT {
			get {
				if (!Store.VatIncludedInPrice)
					return Total;
				
				decimal sumExVat = Total / (1 + ((decimal.Parse(BusinessLogic.Store.vat.ToString(),Store.InputNumberFormatInfo)))/100);
				return sumExVat;
			}
		}

		public decimal TotalIncVAT {
			get {
				if (Store.VatIncludedInPrice)
					return Total;
				
				decimal sumIncVat = Total * (1 + ((decimal.Parse(BusinessLogic.Store.vat.ToString(),Store.InputNumberFormatInfo)))/100);
				
				return sumIncVat;
			}
		}

		public decimal VAT {
			get {
				return (
					TotalIncVAT - TotalExVAT);
			}
		}

        public Status OrderStatus {
			get {
				return (Status) Status.Parse(OrderStatus.GetType(),SqlHelper.ExecuteScalar(_dbdsn,CommandType.Text,"select status from ecommerceOrder where id = " + _OrderId).ToString(),true);
			} set {
				  SqlParameter[] SqlParams = new SqlParameter[] {   new SqlParameter("@value",value.ToString()),
																	new SqlParameter("@orderId",_OrderId.ToString())
                                                                };
				  SqlHelper.ExecuteNonQuery(_dbdsn,CommandType.Text,"update ecommerceOrder set Status = @value where id= @orderId",SqlParams);
			}
		}

		public DateTime OrderTimeStamp {
			get {
				return DateTime.Parse(SqlHelper.ExecuteScalar(_dbdsn,CommandType.Text,"select [timestamp] from ecommerceOrder where id = " + _OrderId).ToString());
			}
		}
		
		public static Order MakeNew(string CustomerXML, string IP,Guid PaymentMethod, string SessionId, string NotificationEmail) {
			SqlParameter[] SqlParams = new SqlParameter[] 
					{
						new SqlParameter("@customerXMl",CustomerXML),
						new SqlParameter("@IP",IP),
						new SqlParameter("@paymentMethod",PaymentMethod.ToString()),
						new SqlParameter("@status",Status.NotConfirmed.ToString()),
						new SqlParameter("@sessionId",SessionId),
						new SqlParameter("@NotificationEmail",NotificationEmail)
					};

			int NewOrderId = int.Parse(SqlHelper.ExecuteScalar(umbraco.GlobalSettings.DbDSN,CommandType.Text,
				"insert into ecommerceOrder (CustomerXML,IP,paymentMethod,status,sessionId,notificationEmail)"+
				" values(@customerXMl,@IP,@paymentMethod,@status,@sessionId,@NotificationEmail) select @@Identity",SqlParams).ToString());
			return new Order(NewOrderId);
		}

		public static Order MakeNew(string CustomerXML, string IP,Guid PaymentMethod, string SessionId, string NotificationEmail, string DbDsn) 
		{
			SqlParameter[] SqlParams = new SqlParameter[] 
					{
						new SqlParameter("@customerXMl",CustomerXML),
						new SqlParameter("@IP",IP),
						new SqlParameter("@paymentMethod",PaymentMethod.ToString()),
						new SqlParameter("@status",Status.NotConfirmed.ToString()),
						new SqlParameter("@sessionId",SessionId),
						new SqlParameter("@NotificationEmail",NotificationEmail)
					};

			int NewOrderId = int.Parse(SqlHelper.ExecuteScalar(DbDsn,CommandType.Text,
				"insert into ecommerceOrder (CustomerXML,IP,paymentMethod,status,sessionId,notificationEmail)"+
				" values(@customerXMl,@IP,@paymentMethod,@status,@sessionId,@NotificationEmail) select @@Identity",SqlParams).ToString());
			return new Order(NewOrderId,DbDsn);
		}

		public XmlDocument CustomerInformation 
		{
			get {
				try {
					string val = SqlHelper.ExecuteScalar(_dbdsn,CommandType.Text, "Select CustomerXML from ecommerceOrder where id = " + _OrderId).ToString();
					XmlDocument x = new XmlDocument();
					x.LoadXml(val);
					return x;
				}
				catch {
					}
				return null;
			}
			set {
				SqlParameter[] SqlParams = new SqlParameter[] 
					{
						new SqlParameter("@customerXMl", value.OuterXml.ToString())
					};

				int NewOrderId = SqlHelper.ExecuteNonQuery(_dbdsn,CommandType.Text,
					"Update ecommerceOrder set CustomerXML = @customerXMl where id =" + _OrderId,SqlParams);
			}
		}

		public OrderLineCollection OrderLines 
		{
			get 
			{
				if (!isInitialized)
					init();
				return _OrderLines;
			}
		}

		public void AddOrderLine(OrderLine l) {
			_OrderLines.Add(l);
			SqlParameter[] SqlParams = new SqlParameter[] 
					{
					new SqlParameter("@orderId",_OrderId),
					new SqlParameter("@productId",l.ProductId),
					new SqlParameter("@productName",l.ProductName),
					new SqlParameter("@quantity",l.Quantity),
					new SqlParameter("@price",l.Price)
					};

			SqlHelper.ExecuteNonQuery(_dbdsn,CommandType.Text,
				"insert into ecommerceOrderLines (orderId,productId,ProductName,quantity,price)"+
					" values(@orderId,@productId,@ProductName,@quantity,@price)",SqlParams);
			Total += (l.Price * l.Quantity);
		}


		public void clearOrderLines() {
			SqlHelper.ExecuteNonQuery(_dbdsn,CommandType.Text,
				"delete from ecommerceOrderLines where orderId = @orderId)",new SqlParameter("@orderId",_OrderId));
		}

		private void init() 
		{
			_OrderLines.Clear();
			SqlDataReader dr = SqlHelper.ExecuteReader(_dbdsn,CommandType.Text,"Select * from ecommerceOrderlines where orderId = " + this.OrderId);
			while (dr.Read())
			{
				OrderLine l = new OrderLine();
				l.Quantity = int.Parse(dr["quantity"].ToString());
				l.ProductName = dr["ProductName"].ToString();
				l.ProductId = dr["ProductId"].ToString();
				l.Price = decimal.Parse(dr["Price"].ToString(),BusinessLogic.Store.InputNumberFormatInfo);
				_OrderLines.Add(l);
			}
			dr.Close();
			isInitialized = true;
		}

		public XmlDocument ToXml() {
			XmlDocument d = new XmlDocument();
			d.AppendChild(d.CreateElement("order"));
			d.DocumentElement.AppendChild(d.ImportNode(this.CustomerInformation.DocumentElement,true));
			
			XmlElement el = d.CreateElement("timestamp");
			el.AppendChild(d.CreateTextNode(OrderTimeStamp.ToUniversalTime().ToString()));
			d.DocumentElement.AppendChild(el);

			 el = d.CreateElement("totalExVat");
			el.AppendChild(d.CreateTextNode(TotalExVAT.ToString(Store.InputNumberFormatInfo)));
			d.DocumentElement.AppendChild(el);

			 el = d.CreateElement("totalInclVat");
			el.AppendChild(d.CreateTextNode(TotalIncVAT.ToString(Store.InputNumberFormatInfo)));
			d.DocumentElement.AppendChild(el);

			el = d.CreateElement("vat");
			el.AppendChild(d.CreateTextNode(VAT.ToString(Store.InputNumberFormatInfo)));
			d.DocumentElement.AppendChild(el);
			
			
			el = d.CreateElement("vatpercentage");
			el.AppendChild(d.CreateTextNode(Store.vat.ToString()));
			d.DocumentElement.AppendChild(el);

			el = d.CreateElement("paymentmethod");
			el.AppendChild(d.CreateTextNode(PaymentMethod.Name));
			d.DocumentElement.AppendChild(el);
			
			XmlElement orderlines = d.CreateElement("orderlines");
			foreach (OrderLine l in this.OrderLines) {
				orderlines.AppendChild(l.toXml(d));
			}
			d.DocumentElement.AppendChild(orderlines);
			return d;
		}

		
		#region "Low abstraction"

		/* Low abstraction method used to display orders in grid
		 * by pages - this greatly increases performace in the umbraco interface
		 */
		public static DataTable GetOrdersByPageAndStatus(int PageSize, int CurrentPage, Status s) 
		{
			SqlParameter[] SqlParams = new SqlParameter[] {
															  new SqlParameter("@Status",s.ToString()),
															  new SqlParameter("@CurrentPage",CurrentPage),
															  new SqlParameter("@PageSize ",PageSize),
			};

			SqlDataReader dr = SqlHelper.ExecuteReader(umbraco.GlobalSettings.DbDSN,CommandType.StoredProcedure,"ecommerceGet_Orders_By_Page_And_Status",SqlParams);
			DataTable dt = new DataTable();
			dt.Columns.Add("Id");
			dt.Columns.Add("OrderId");
			dt.Columns.Add("Total");
			dt.Columns.Add("Timestamp");
			dt.Columns.Add("PaymentStatus");

			while (dr.Read()) 
			{
				DataRow dr1 = dt.NewRow();
				dr1["Id"] = dr["Id"].ToString();
				dr1["OrderId"] = dr["OrderId"].ToString();
				dr1["Total"] = decimal.Parse(dr["Total"].ToString(),BusinessLogic.Store.InputNumberFormatInfo).ToString("c",BusinessLogic.Store.NumberFormatInfo);
				dr1["Timestamp"] = dr["Timestamp"].ToString();
				dr1["PaymentStatus"] = new Order(int.Parse(dr["OrderId"].ToString())).PaymentMethod.CashCollected(int.Parse(dr["id"].ToString()));
				dt.Rows.Add(dr1);
			}
		
			dr.Close();
			return dt;
		}

		public static int OrderCountByStatus(Status s) {
			SqlParameter[] SqlParams = new SqlParameter[] 
				{new SqlParameter("@status",s.ToString())};
			return int.Parse(SqlHelper.ExecuteScalar(umbraco.GlobalSettings.DbDSN,CommandType.Text,"select count(id) as tmp from ecommerceOrder where status = @status",SqlParams).ToString());
		}
#endregion

		public enum Status 
		{
			NotConfirmed,
			Confirmed,
			Accepted,
			Rejected
		}
	}
}